/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package projetosustentabilidade;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

/**
 *
 * @author Diego
 */
public class Excel {

     private ArrayList<Paises> objPaises;// = new ArrayList<Paises>();
    private ArrayList<Taxas> objTaxas;// = new ArrayList<Taxas>();
    private ArrayList<Atributos> objAtributos;// = new ArrayList<Atributos>();

    public Excel(){
        objPaises = new ArrayList<Paises>();
        objTaxas = new ArrayList<Taxas>();
        objAtributos = new ArrayList<Atributos>();

        try {
            lerExcelAtributos();
            lerExcelPaises();
            lerExcelTaxas();
        } catch (IOException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (BiffException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }




    public void lerExcelAtributos() throws IOException, BiffException {
         WorkbookSettings ws = new WorkbookSettings();

        ws.setEncoding("ISO-8859-1");
        Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"),ws);
        int idAtributo;
        String nome;
        String classficacaoTripe;
        String descricao;

        //Numero de atributos
        for (int k = 2; k < 15; k++) {
            Sheet sheet = workbook.getSheet(k);
            //ClassificaçãoTripe
            Cell celula = sheet.getCell(0, 0);
            classficacaoTripe = celula.getContents();
            //Nome
            celula = sheet.getCell(1, 0);
            nome = celula.getContents();
            //ID
            celula = sheet.getCell(9, 0);
            idAtributo = Integer.parseInt(celula.getContents());
            //Decrição
            celula = sheet.getCell(8, 0);
            descricao = celula.getContents();
            objAtributos.add(new Atributos(idAtributo, nome, classficacaoTripe, descricao));
         //   System.out.println("Atributo: "+idAtributo+" "+" "+nome+" "+classficacaoTripe+" "+descricao);
        }


    }

    public void lerExcelPaises() throws IOException, BiffException{
         WorkbookSettings ws = new WorkbookSettings();

        ws.setEncoding("ISO-8859-1");
        Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"),ws);
        Sheet sheet = workbook.getSheet(0);
        int idPaises;
        String continente;
        String pais;
        String info;
        //Numero de paises
        for (int i = 3 ; i<103; i++){
            //País
            Cell celula = sheet.getCell(1, i);
            pais = celula.getContents();
            //Continte
            celula = sheet.getCell(4, i);
            continente = celula.getContents();
            //Id paises
            celula = sheet.getCell(0, i);
            idPaises = Integer.parseInt(celula.getContents());
            //Informação
            celula = sheet.getCell(5, i);
            info = celula.getContents();
            objPaises.add(new Paises(idPaises, continente, pais, info));
         //   System.out.println("Paises: "+idPaises+" "+" "+continente+" "+pais+" "+info);
        }
    }

    public void lerExcelTaxas() throws IOException, BiffException{
        WorkbookSettings ws = new WorkbookSettings();

        ws.setEncoding("ISO-8859-1");
        Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"),ws);
        float [] intervalos =  new float[6];
        int idAtributo;
        int idPaises;
        int aux =0;

        String pontoVirgula;

        for (int k =2; k<15; k++){
            Sheet sheet = workbook.getSheet(k);
            for (int i=1;i<101;i++){
                for (int j=2;j<8;j++){
                    Cell celula = sheet.getCell(j, i);
                    pontoVirgula = celula.getContents().replace(',', '.');
                    intervalos[aux] = Float.parseFloat(pontoVirgula);
                    //System.out.println("Intervalos: "+intervalos[aux]);
                    aux++;
                }
                aux = 0;
                idPaises = objPaises.get(i-1).getIdPaises();
                idAtributo = objAtributos.get(k-2).getIdAtributo();
                objTaxas.add(new Taxas(idAtributo, idPaises, intervalos[0], intervalos[1], intervalos[2], intervalos[3], intervalos[4], intervalos[5]));
             //   System.out.println("Atributo: "+idAtributo+" "+" "+idPaises+" "+" "+intervalos[0]+" "+intervalos[1]+" "+intervalos[2]+" "+intervalos[3]+" "+intervalos[4]+" "+intervalos[5]);
            }
        }

    }

    public ArrayList<Atributos> getObjAtributos() {
        for (int i = 0;i<objAtributos.size();i++){

            //System.out.println("Atributo: "+objAtributos.get(i).getIdAtributo()+" "+" "+objAtributos.get(i).getNome()+" "+objAtributos.get(i).getClassficacaoTripe());
        }
        return objAtributos;
    }

    public ArrayList<Paises> getObjPaises() {
        return objPaises;
    }

    public ArrayList<Taxas> getObjTaxas() {
        return objTaxas;
    }

}